/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package df.bean.interfacefile;

import df.bean.db.conn.DBConnection;
import java.sql.ResultSet;
import java.sql.Statement;
import df.bean.db.conn.DBConn;
import df.bean.obj.util.JDate;

/**
 *
 * @author nopphadon
 */
public class ExportDFToAPBean extends InterfaceTextFileBean {
    private ResultSet rs;
    private Statement stm;
    private String payment_date = "";
    private String transaction_date = "";
    
    @Override
    public boolean insertData(String fn, DBConnection d) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    public void setPaymentDate(String s){
    	this.payment_date = s;
    }
    public void setTransactionDate(String s){
    	this.transaction_date = s;
    }

    @Override
    public boolean exportData(String fn, String hp, String type, String year, String month, DBConn d, String path) {
        String[] sub_data = null;
        boolean status = true;
        String[][] temp_data = null;
        int line = 0;
        String dat = "SELECT PM.HOSPITAL_CODE, PM.DOCTOR_CODE, 'PY-DF', PM.DOCTOR_CODE+PM.MM+PM.YYYY, '" + //0-3
        			 this.transaction_date+"', '"+this.transaction_date+"', PM.DR_NET_PAID_AMT, " + //4-6
        			 "0, '"+month+"/"+year+"', '231014', DR.DEPARTMENT_CODE, '', 'CHK', '15', '"+ //7-13
        			 this.payment_date+"', 'TMB', 'C01', 'comment', 'line', PM.DR_NET_PAID_AMT, "+ //14-19
                     "'N', 'DFACCRUE', 0, 0, 0 "+ //20-24
                     "FROM PAYMENT_MONTHLY PM LEFT OUTER JOIN DOCTOR DR ON PM.DOCTOR_CODE = DR.CODE "+
                     "WHERE (PM.BATCH_NO IS NULL OR PM.BATCH_NO = '') AND "+
                     "PM.YYYY LIKE '"+year+"' AND PM.MM LIKE '"+month+"' AND PM.PAYMENT_MODE_CODE = 'CQ' AND "+
                     "PM.DR_NET_PAID_AMT > 0 AND PM.HOSPITAL_CODE LIKE '"+hp+"'";
        String transfer_amount = 
		        	"SELECT PM.HOSPITAL_CODE, PM.DOCTOR_CODE, 'PY-DF', PM.DOCTOR_CODE+PM.MM+PM.YYYY, '" + //0-3
					 this.transaction_date+"', '"+this.transaction_date+"', PM.DR_NET_PAID_AMT, " + //4-6
					 "0, '"+month+"/"+year+"', '231014', DR.DEPARTMENT_CODE, '', 'CHK', '15', '"+ //7-13
					 this.payment_date+"', 'TMB', 'C03', 'pay cheque', 'line', PM.DR_NET_PAID_AMT, "+ //14-19
		            "'N', 'DFACCRUE', 0, 0, 0 "+ //20-24
		            "FROM PAYMENT_MONTHLY PM LEFT OUTER JOIN DOCTOR DR ON PM.DOCTOR_CODE = DR.CODE "+
		            "WHERE (PM.BATCH_NO IS NULL OR PM.BATCH_NO = '') AND "+
		            "PM.YYYY LIKE '"+year+"' AND PM.MM LIKE '"+month+"' AND PM.PAYMENT_MODE_CODE = 'CQ' AND "+
		            "PM.DR_NET_PAID_AMT > 0 AND PM.HOSPITAL_CODE LIKE '"+hp+"' "+
		            "UNION "+
		        	"SELECT PM.HOSPITAL_CODE, '0000001001', 'PY-DF', '0000001001'+PM.MM+PM.YYYY, '" +
		        	this.transaction_date+"', '"+this.transaction_date+"', SUM(PM.DR_NET_PAID_AMT), " +
		        	"0, PM.MM+'/'+PM.YYYY, '231014', '', '', 'CHK', '15', '"+
		        	this.payment_date+"', 'SCB', 'C01', 'bank transfer', 'line', SUM(PM.DR_NET_PAID_AMT), " +
		        	"'N', 'DFACCRUE', 0, 0, 0 "+
		        	"FROM PAYMENT_MONTHLY PM LEFT OUTER JOIN DOCTOR DR ON PM.DOCTOR_CODE = DR.CODE "+
		        	"WHERE (PM.BATCH_NO IS NULL OR PM.BATCH_NO = '') AND "+
		        	"PM.YYYY LIKE '"+year+"' AND PM.MM LIKE '"+month+"' AND PM.PAYMENT_MODE_CODE = 'B' AND "+
		        	"PM.DR_NET_PAID_AMT > 0 AND PM.HOSPITAL_CODE LIKE '"+hp+"' "+
		        	"GROUP BY PM.HOSPITAL_CODE, '0000001001'+PM.MM+PM.YYYY, PM.MM+'/'+PM.YYYY "+
        			"UNION "+
		        	"SELECT EP.HOSPITAL_CODE, 'OTH-0043', 'PY-DF', 'OTH-0043'+EP.MM+EP.YYYY, '" +
		        	this.transaction_date+"', '"+this.transaction_date+"', SUM(EP.AMOUNT), " +
		        	"0, EP.MM+'/'+EP.YYYY, '231014', '', '', 'CHK', '15', '"+
		        	this.payment_date+"', 'TMB', 'C03', 'expense', 'line', SUM(EP.AMOUNT), " +
		        	"'N', 'DFACCRUE', 0, 0, 0 "+
		        	"FROM TRN_EXPENSE_DETAIL EP "+
		        	"WHERE EP.YYYY LIKE '"+year+"' AND EP.MM LIKE '"+month+"' AND "+
		        	"EP.HOSPITAL_CODE = '"+hp+"' AND EXPENSE_CODE = 'EXP_CU' "+
		        	"GROUP BY EP.HOSPITAL_CODE, 'OTH-0043'+EP.MM+EP.YYYY,EP.MM+'/'+EP.YYYY";

        try {
        	//System.out.println(dat);
            setFileName(path);//set filename read
            temp_data = d.query(transfer_amount);
            sub_data = new String[temp_data.length];
            if(temp_data.length<1){
            	sub_data = new String[1];
            	sub_data[0] = ""+this.transaction_date+":"+this.payment_date;
            }
            line = 1;
            for(int i = 0; i<temp_data.length; i++){
                sub_data[i] = temp_data[i][0]+"|"+ //hospital code (Business Unit)
                              temp_data[i][1]+"|"+ //Vendor ID
                              temp_data[i][2]+"|"+ //Vendor Location
                              temp_data[i][3]+"|"+ //Invoice No.
                              temp_data[i][4]+"|"+ //Payment Date (Invoice Date)
                              temp_data[i][5]+"|"+ //Payment Date (Accounting Date)
                              temp_data[i][6]+"0|"+ //DR Net Paid Amount (Gross Amount)
                              temp_data[i][7]+".000|"+ //Vat Amt
                              temp_data[i][8]+"|"+ //Line
                              temp_data[i][9]+"|"+ //Account
                              temp_data[i][10]+"|"+ //Department
                              "|"+ //11 Product
                              "CHK|"+ //12 Payment Method
                              "15|"+ //13 Payment Term
                              temp_data[i][14]+"|"+ //Payment Date (Payment Due)
                              temp_data[i][15]+"|"+ //Bank Code
                              temp_data[i][16]+"|"+ //Bank Account
                              temp_data[i][17]+"|"+ //Comment
                              "1|"+ //18 Line No.
                              temp_data[i][19]+"0|"+ //Line invoice Amount
                              temp_data[i][20]+"|"+ //Withholding
                              temp_data[i][21]+"|"+ //Accounting Template
                              temp_data[i][22]+"|"+ //Misc Charge
                              temp_data[i][23]+"|"+ //Freight Amount
                              temp_data[i][24]+"";  //Sales Tax Amount
                line++;
                System.out.println("Num: "+line+" : "+sub_data[i]);
            }
        }catch(Exception e){
            System.out.println(e);
        }
        writeFileNew(sub_data);
        return status;
    }
}
